Unit. 5 -- Common Modeling Situations

The Four-Step Design Method for Design a Fact Table

1. Step 1: Choosing the data mart

• Start with a sigle-source data mart, not multiple-source one.

2. Step 2: Deside the fact table grain

• Choose as low-grain as possible

            - The lower the level of granularity, the more robust the design

             - Easily handle unexpected queries and additional new data elements

• Examples

           - Individual transaction level

               . Each sales transaction

                       . Each insurance claim

                       . Each ATM transaction

            - Individual snapshot level

                      . Each daily product sales total in each store

                      . Each monthly account snapshot

            - Line_item level

                    . Each line-item on each order(shipment's invoice)

                    . Each coverage in each individual insurance level

3. Step 3: Choosing the dimensions

• The best dimesions are the single-valued descriptors attached to a set of measurements.
• The granularity of a dimension cannot be lower than the fact table granularity
• The granularity of a dimension could be higher than the fact table granularity.

        Example:     Fact: Month level

                            Time dimension: can be year, but not daily level

4. Step 4: Choose the facts

• Facts should always be specified to the grain of the fact table
• Aggregate or summary data should be in different records and different fact tables

Families of Fact Table

• In reality even a single data mart is a coordinated set of fact tables.
• They need to use conformed dimensions.
• There are four main reasons to build multiple fact tables in a data mart.

Reason 1: Supply Chains, Value Chains, Value Circles

Multiple fact tables are needed to support a business with many processes. Each process spawns one or more fact tables.

• Supply Chain

   A product moves from the acquisition of raw materials through to the finished good.

• Raw material production, such as mining or growing
• Ingredient purchasing
• Ingredient delivery
• Ingredient inventory
• Bill of materials
• Manufacturing process control
• Manufacturing costs
• Packaging
• Trans-shipping to warehouse
• Finished goods inventory

• Value Chain or Demand Chain

    After a product enters finished goods inventory in a warehouse, it is thought of as being part of the demand chain, or value chain.

• Finished goods inventory
• Maunfacturing shipments
• Distributor inventory
• Distributor shipments
• Retail inventory
• Retail sales

• Value circle

    In other kind of business, all the entities may be performing or measuring the same kind of transaction,

• Hospitals
• Clincs
• Long-term care facilities
• Physician offices
• Pharmacies
• Pharmaceutical manufacturers
• Laboratories
• Employers
• Insurance companies
• Government agencies like Medicare

Reason 2: Heterogeneous Product Schemas

Multiple fact tables are needed when a business has heterogeneous products that have naturally different facts but a single customer base.

• Retail bank offers checking accouts, saving accounts, mortgage loans, credit cards, etc...
• Global view slice and dice all the accouts of all types at the same time, create a single core fact table

• line-of-business view focuses on one line of business, create a custom fact table for each product

Reason 3: Transactions and Snapshot Schemas

• Some businesses have a rich transactional history
• Transactions are not little pieces of revenue
• Need two fact tables to represent all the activity:

        -  Transactions fact table is usually built first

  -  Snapshot fact tables capture the periodic snapshots.

Reason 4: Aggregates

• Summary tables along one or more dimensions
• Aggregates are stored in seperate tables from the fact table

Factless Fact Tables

• Fact tables with no meaningful measures
• Event tracking system

        - Example: When a salesperson didn't get an order

        - Example: Record student attendance of classes

• Add a dummy fact-like field: Attendance

• Coverage system

        - Use it when a primary fact table is sparse

        - Regular sales fact table cannot answer: Which products on promotion did not sell?

        - The coverage fact table will contain a row for each item on promotion

        - Query processing of the above query:

             . Find list of products in promotion from the coverage fact table

            . MINUS

            . Find the list of products that did sell

            . Perform MINUS

        - The coverage tables avoids storing explicit zeroes